package com.xue.web.utils;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.*;

public class JdbcTools {

    public JdbcTools(String configPath) {
        this.configPath = configPath;
    }
    private final String configPath;
    private Connection connection;
    private ResultSet resultSet;
    private PreparedStatement preparedStatement;
    /**获取连接
     * @param configPath 传入个配置文件名称
     * @return
     * @throws IOException
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    private Connection getConnection(String configPath) throws IOException, ClassNotFoundException, SQLException {
        InputStream resourceAsStream = JdbcTools.class.getClassLoader().getResourceAsStream(configPath);
        Properties properties = new Properties();
        properties.load(resourceAsStream);
        Class.forName((String) properties.get("driverClass"));
        connection = DriverManager.getConnection((String) properties.get("url"), (String) properties.get("name"), (String) properties.get("pwd"));
        return connection;
    }
    /**无占位符的sql
     * @param sql
     * @throws SQLException
     * @throws IOException
     * @throws ClassNotFoundException
     */
    public Map<String, List<Object>> executeQuery(String sql) throws SQLException, IOException, ClassNotFoundException {
        Map<String, List<Object>> query = query(sql, null);
        return query;
    }
    /**
     * 查询
     *
     * @param sql    有占位符的sql
     * @param params 占位符的需要替换的东西 [100,"kveans"]
     * @throws SQLException
     * @throws IOException
     * @throws ClassNotFoundException
     */
    public Map<String, List<Object>> executeQuery(String sql, Object[] params) throws SQLException, IOException, ClassNotFoundException {
        Map<String, List<Object>> query = query(sql, params);
        return query;
    }
    /**用于纠正 executeQuery 的错误
     *
     * @param sql
     * @param params
     * @return
     * @throws SQLException
     * @throws IOException
     * @throws ClassNotFoundException
     */
    private Map<String, List<Object>> query(String sql, Object[] params) throws SQLException, IOException, ClassNotFoundException {

        List<String> numericField = new ArrayList<String>();//字段集合
        Map<String, List<Object>> searchResult = new HashMap<String, List<Object>>();

        if (params == null || params.length == 0) {
            //getConnection(configPath)获取连接,PreparedStatement准备执行环境
            preparedStatement = getConnection(configPath).prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();

            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();//获取字段数量


            for (int i = 1; i <= columnCount; i++) {
                numericField.add(metaData.getColumnName(i));
            }
            for (int i = 0; i < columnCount; i++) {
                List<Object> temp = new ArrayList<Object>();
                searchResult.put(numericField.get(i), temp);

            }
            while (resultSet.next()) {

                List<Object> temp = new ArrayList<Object>();

                for (int i = 1; i <= columnCount; i++) {
                    temp.add(resultSet.getObject(i));
                }
                for (int i = 0; i < columnCount; i++) {
                    searchResult.get(numericField.get(i)).add(temp.get(i));
                }
                temp.clear();
            }
        } else {
            preparedStatement = getConnection(configPath).prepareStatement(sql);
            for (int i = 1; i <= params.length; i++) {
                preparedStatement.setObject(i, params[i - 1]);

            }
            resultSet = preparedStatement.executeQuery();

            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();//获取字段数量


            for (int i = 1; i <= columnCount; i++) {
                numericField.add(metaData.getColumnName(i));
            }
            for (int i = 0; i < columnCount; i++) {
                List<Object> temp = new ArrayList<Object>();
                searchResult.put(numericField.get(i), temp);

            }
            while (resultSet.next()) {

                List<Object> temp = new ArrayList<Object>();

                for (int i = 1; i <= columnCount; i++) {
                    temp.add(resultSet.getObject(i));
                }
                for (int i = 0; i < columnCount; i++) {
                    searchResult.get(numericField.get(i)).add(temp.get(i));
                }
                temp.clear();
            }

        }
        close();

        return searchResult;
    }
    /**改
     *
     * @param sql
     * @return
     * @throws SQLException
     * @throws IOException
     * @throws ClassNotFoundException
     */
    public boolean  executeUpdate(String sql) throws SQLException, IOException, ClassNotFoundException {
        preparedStatement = getConnection(configPath).prepareStatement(sql);
        int i = preparedStatement.executeUpdate();
        close();
        return i==1;
    }

    /**增加
     *
     * @param sql
     * @param params
     * @return
     * @throws SQLException
     * @throws IOException
     * @throws ClassNotFoundException
     */
    public boolean  executeInsert(String sql, Object[] params) throws SQLException, IOException, ClassNotFoundException {
        preparedStatement = getConnection(configPath).prepareStatement(sql);
        for (int i = 1; i <= params.length; i++) {
            preparedStatement.setObject(i, params[i - 1]);
        }
        int i = preparedStatement.executeUpdate();
        close();
        return i==1;
    }
    /**
     * 关流
     *
     * @throws SQLException
     */
    private void close() throws SQLException {
        if (resultSet != null) {
            resultSet.close();
        }
        if (preparedStatement != null) {
            preparedStatement.close();
        }

        if (connection != null) {
            connection.close();
        }

    }
}
